package DBUtil;

import java.sql.*;


public class UserDao {
    Connection conn;
    ResultSet rs = null;

    UserDao() {//初始化自动连接到数据库
        DBUtil.connetcDB();
        this.conn = DBUtil.getConnection();//获得链接
    }

    public void add(User user) {//返回是否添加成功
        String sql = "insert into userinfo(ID,username,password) values(?,?,?)";
        try {
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, user.getID());
            st.setString(2, user.getUsername());
            st.setString(3, user.getPassword());
            int result = st.executeUpdate();
            System.out.println("成功添加用户" + user.getUsername() + "，影响行数：" + result + "\n");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void queryByName(String name) {//返回一个javasql记录
        String sql = "SELECT * from userinfo where username = ?";//查询
        try {
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, name);
            rs = st.executeQuery();
            while (rs.next()) {
                String ID = rs.getString(1);
                String name1 = rs.getString(2);
                String password = rs.getString(3);
                System.out.println("成功查询到以下数据：");
                System.out.println("ID\t\t用户名\t\t密码");//输出
                System.out.println(ID + "\t\t" + name1 + "\t\t\t" + password + "\n");//输出
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void deleteByName(String name) {
        String sql = "delete from userinfo where username = ?";
        try {
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, name);
            int result = st.executeUpdate();
            System.out.println("成功删除用户：" + name + "\t影响行数：" + result + "\n");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void updatePassword(String name, String password) {
        String sql = "update userinfo set password = ? where username = ?";
        try {
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, password);
            st.setString(2, name);
            int n = st.executeUpdate();
            System.out.println("更新用户：" + name + "成功，影响行数：" + n + "\n");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void queryAll() {
        String sql = "select * from userinfo";
        try {
            PreparedStatement st = conn.prepareStatement(sql);
            rs = st.executeQuery(sql);
            System.out.println("全部用户信息如下：");
            System.out.println("ID\t\t用户名\t\t密码");//输出
            while (rs.next()) {
                String ID = rs.getString(1);
                String name = rs.getString(2);
                String password = rs.getString(3);
                System.out.println(ID + "\t\t" + name + "\t\t\t" + password);//输出
            }
            System.out.println("\n");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

